package com.kfm.dao;

import com.kfm.entity.Student;
import com.kfm.entity.StudentDTO;
import com.kfm.util.DBUtil;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class StudentDao {

    private DBUtil<Student> dbUtil = new DBUtil<>();

    public List<Student> selectAll() throws SQLException {
        String sql = "select * from student";
        return dbUtil.queryList(sql, Student.class);
    }

    public List<StudentDTO> selectAllWithTeacherNameAndClassName(int start, int size) throws SQLException {
        DBUtil<StudentDTO> dbUtil = new DBUtil<>();
        String sql = "select student.*, u.name as teacher_name, c.name class_name  from student left join user u on u.id = student.teacher\n" +
                "left join classes c on student.class_id = c.id limit ?, ?";
        return dbUtil.queryList(sql, StudentDTO.class, start, size);
    }

    public Student selectById(String id ) {
        String sql = "select id, name, gender, birthday, teacher, class_id from student where id = ?";
        try {
            return dbUtil.queryOne(sql, Student.class, id);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public int save(Student student) throws SQLException {
        // 动态 sql
        StringBuffer columns = new StringBuffer("insert into student(id, name ");
        StringBuffer values = new StringBuffer(" values(?, ? ");
        List<Object> params = new ArrayList<>();
        params.add(student.getId());
        params.add(student.getName());

        if (student.getGender() != null){
            columns.append(" ,gender");
            values.append(" ,?");
            params.add(student.getGender());
        }
        if (student.getBirthday() != null){
            columns.append(" ,birthday");
            values.append(" ,?");
            params.add(student.getBirthday());

        }
        if (student.getTeacher() != null){
            columns.append(" ,teacher");
            values.append(" ,?");
            params.add(student.getTeacher());
        }

        if (student.getClassId() != null){
            columns.append(" ,class_id");
            values.append(" ,?");
            params.add(student.getClassId());
        }

        columns.append(")");
        values.append(")");

        String sql = columns.toString() + values.toString();

        return dbUtil.save(sql, false, params.toArray());
    }

    public int updateById(Student student) throws SQLException {
        StringBuffer columns = new StringBuffer("update student set ");
        List<Object> params = new ArrayList<>();

        if (student.getName() != null){
            columns.append(" name = ?,");
            params.add(student.getName());
        }
        if (student.getGender() != null){
            columns.append(" gender = ?,");
            params.add(student.getGender());
        }
        if (student.getBirthday() != null){
            columns.append(" birthday = ?,");
            params.add(student.getBirthday());
        }
        if (student.getTeacher() != null){
            columns.append(" teacher = ?,");
            params.add(student.getTeacher());
        }
        if (student.getClassId() != null){
            columns.append(" class_id = ?");
            params.add(student.getClassId());
        }

        String sql = columns.toString();
        if (sql.endsWith(",")){
            sql = sql.substring(0, sql.length() - 1);
        }

        sql = sql + " where id = ?";
        params.add(student.getId());
        return dbUtil.update(sql, params.toArray());
    }

    public int count() throws SQLException {
        String sql = "select count(*) from student";
        ResultSet query = dbUtil.query(sql);

        if (query.next()) {
            return query.getInt(1);
        }
        return -1;
    }

}
